import * as DocumentPicker from "expo-document-picker";
import * as FileSystem from "expo-file-system"; // Import FileSystem
// 增加对于用户媒体库的控制
import * as MediaLibrary from "expo-media-library";
import * as SQLite from "expo-sqlite";

import {
  Alert,
  Button,
  FlatList,
  StyleSheet,
  Text,
  TextInput,
  View,
} from "react-native";
import React, { useEffect, useState } from "react";

const SqliteExample = () => {
  const [data, setData] = useState([]);
  const [newValue, setNewValue] = useState("");
  const [newIntValue, setNewIntValue] = useState("");
  const [queryId, setQueryId] = useState("");
  const [saveDirectory, setSaveDirectory] = useState(
    FileSystem.documentDirectory
  );
  // 打开数据库和执行相关操作
  useEffect(() => {
    const initDatabase = async () => {
      // 这里默认初始化的时候，创建一个名称为 test.db 的数据库文件，如果没有创建出来
      const db = await SQLite.openDatabaseAsync("test.db"); // 确保使用正确的数据库文件名
      // 打印数据库文件路径
      console.log(
        "Database file path:",
        `${FileSystem.documentDirectory}SQLite/test.db`
      );

      // 执行 SQL 语句
      await db.execAsync(`
        PRAGMA journal_mode = WAL;
        CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY NOT NULL, value TEXT NOT NULL, intValue INTEGER);
        INSERT OR IGNORE INTO test (value, intValue) VALUES ('test1', 123);
        INSERT OR IGNORE INTO test (value, intValue) VALUES ('test2', 456);
        INSERT OR IGNORE INTO test (value, intValue) VALUES ('test3', 789);
      `);

      // 获取所有数据
      const allRows = await db.getAllAsync("SELECT * FROM test");
      setData(allRows);
    };

    initDatabase();
  }, []);

  const insertData = async () => {
    if (newValue && newIntValue) {
      const db = await SQLite.openDatabaseAsync("test.db"); // 确保使用正确的数据库文件名
      await db.runAsync(
        "INSERT INTO test (value, intValue) VALUES (?, ?)",
        newValue,
        parseInt(newIntValue)
      );
      setNewValue("");
      setNewIntValue("");
      await refreshData();
      Alert.alert("Data inserted successfully");
    } else {
      Alert.alert("Please provide both value and intValue");
    }
  };

  /**
   * 移动文件位置
   * @param uri
   * @returns
   */
  const moveRecordingToPublicDirectory = async (uri) => {
    try {
      // Request media library permissions
      const { status } = await MediaLibrary.requestPermissionsAsync();
      if (status !== "granted") {
        alert("Permission to access media library is required!");
        return;
      }

      const newUri = `${FileSystem.documentDirectory}SQLite/test.db`;
      // Move the file to the new location
      await FileSystem.moveAsync({
        from: uri,
        to: newUri,
      });
      // Save the file to the media library
      const asset = await MediaLibrary.createAssetAsync(newUri);
      // TODO:这里第一个参数：默认是创建的文件夹名称：sqlFold
      await MediaLibrary.createAlbumAsync("sqlFold", asset, false);
      console.log("File moved to public directory:", newUri);
      alert("sqlite数据库导出成功 saved to public directory!");
    } catch (error) {
      console.error("Error moving recording to public directory", error);
      alert("Failed to save recording to public directory.");
    }
  };
  const deleteData = async () => {
    if (data.length > 0) {
      const db = await SQLite.openDatabaseAsync("test.db"); // 确保使用正确的数据库文件名
      await db.runAsync("DELETE FROM test WHERE id = ?", [data[0].id]);
      await refreshData();
      Alert.alert("Data deleted successfully");
    } else {
      Alert.alert("No data to delete");
    }
  };

  const queryData = async () => {
    if (queryId) {
      const db = await SQLite.openDatabaseAsync("test.db"); // 确保使用正确的数据库文件名
      const result = await db.getFirstAsync("SELECT * FROM test WHERE id = ?", [
        parseInt(queryId),
      ]);
      if (result) {
        Alert.alert(
          "Query Result",
          `ID: ${result.id}\nValue: ${result.value}\nIntValue: ${result.intValue}`
        );
      } else {
        Alert.alert("No data found for the given ID");
      }
    } else {
      Alert.alert("Please provide an ID to query");
    }
  };

  const refreshData = async () => {
    const db = await SQLite.openDatabaseAsync("test.db"); // 确保使用正确的数据库文件名
    const allRows = await db.getAllAsync("SELECT * FROM test");
    setData(allRows);
  };

  /**
   * 移动文件到公共目录：媒体文件夹公共目录：默认Picture下的
   */
  const moveMediaToPublicDirectory = async () => {
    try {
      const dbFilePath = `${FileSystem.documentDirectory}SQLite/test.db`;
      // 先移动，将当前的位置数据库，移动到公共目录下
      await moveRecordingToPublicDirectory(dbFilePath);

      alert("移动成功");
    } catch (error) {
      alert("移动失败");
    }
  };

  // 导出 SQLite 数据库
  const exportDatabase = async () => {
    try {
      // 获取 SQLite 数据库文件路径
      const dbFilePath = `${FileSystem.documentDirectory}SQLite/test.db`;

      // 读取数据库文件内容
      const fileData = await FileSystem.readAsStringAsync(dbFilePath, {
        encoding: FileSystem.EncodingType.Base64,
      });

      // 创建导出文件路径
      const exportPath = `${FileSystem.documentDirectory}/SQLite/test_backup.db`;

      // 写入导出文件
      await FileSystem.writeAsStringAsync(exportPath, fileData, {
        encoding: FileSystem.EncodingType.Base64,
      });

      Alert.alert("Success", `Database exported successfully to ${exportPath}`);
    } catch (error) {
      Alert.alert("Error", "Failed to export the database: " + error.message);
    }
  };

  return (
    <View style={styles.container}>
      <View style={styles.inputContainer}>
        <TextInput
          style={styles.input}
          placeholder="Enter value"
          value={newValue}
          onChangeText={setNewValue}
        />
        <TextInput
          style={styles.input}
          placeholder="Enter intValue"
          keyboardType="numeric"
          value={newIntValue}
          onChangeText={setNewIntValue}
        />
        <Button title="Insert Data" onPress={insertData} />
      </View>
      <View style={styles.inputContainer}>
        <Button title="Delete First Data" onPress={deleteData} />
      </View>
      <View style={styles.inputContainer}>
        <TextInput
          style={styles.input}
          placeholder="Enter ID to Query"
          keyboardType="numeric"
          value={queryId}
          onChangeText={setQueryId}
        />
        <Button title="Query Data" onPress={queryData} />
      </View>
      <Button title="Refresh Data" onPress={refreshData} />
      <Button title="Export Database" onPress={exportDatabase} />
      <Button title="移动文件" onPress={moveMediaToPublicDirectory} />
      <FlatList
        data={data}
        keyExtractor={(item) => item.id.toString()}
        renderItem={({ item }) => (
          <View style={styles.item}>
            <Text>ID: {item.id}</Text>
            <Text>Value: {item.value}</Text>
            <Text>IntValue: {item.intValue}</Text>
          </View>
        )}
      />
    </View>
  );
};

const styles = StyleSheet.create({
  container: {
    flex: 1,
    justifyContent: "center",
    alignItems: "center",
    padding: 20,
  },
  inputContainer: {
    marginBottom: 20,
  },
  input: {
    borderWidth: 1,
    borderColor: "#ccc",
    padding: 10,
    borderRadius: 5,
    marginBottom: 10,
    width: 200,
  },
  item: {
    padding: 10,
    borderBottomWidth: 1,
    borderBottomColor: "#ccc",
  },
});

export default SqliteExample;
